Skip to content

The function psycopg2.extras.register_composite doesn't regard the search path #1487

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
papel opened this issue Aug 22, 2022 · 1 comment
Closed

Comments

@papel
Copy link

papel commented Aug 22, 2022

Please complete the following information:

  • OS: Linux 5.10.0-13-amd64
  • Psycopg version: 2.9.3
  • Python version: 3.9.2
  • PostgreSQL version: 13.7
  • pip version: 20.3.4

Describe the bug
I am trying to use psycopg2.extras.register_composite using the search path.

I use the following connection to use the schema appdata instead of public without having to put the prefix with the name of the schema. It works for queries and the tables don't need the prefix, but the function register_composite isn't affected by it.

	psycopg2.connect(
		host="localhost",
		port="5432",
		user="myuser",
		database= ...,
		password=...,
		options=f'-c search_path="appdata"',
		cursor_factory=psycopg2.extras.NamedTupleCursor
	)

	#Error: PostgreSQL type 'bivalue' not found
	psycopg2.extras.register_composite('bivalue', cur);

	#This works
	psycopg2.extras.register_composite('appdata.bivalue', cur);

I want to avoid the name of the schema repeated in many places in the app. It should be set only in one place.

@dvarrazzo
Copy link
Member

You are right: this function has a pretty old and has a weak and broken resolution of the type name. I am surprised nobody found this problem earlier.

psycopg2/lib/extras.py

Lines 1078 to 1097 in 3e7bb8d

# Use the correct schema
if '.' in name:
schema, tname = name.split('.', 1)
else:
tname = name
schema = 'public'
# column typarray not available before PG 8.3
typarray = conn.info.server_version >= 80300 and "typarray" or "NULL"
# get the type oid and attributes
curs.execute("""\
SELECT t.oid, %s, attname, atttypid
FROM pg_type t
JOIN pg_namespace ns ON typnamespace = ns.oid
JOIN pg_attribute a ON attrelid = typrelid
WHERE typname = %%s AND nspname = %%s
AND attnum > 0 AND NOT attisdropped
ORDER BY attnum;
""" % typarray, (tname, schema))

The consensus, when a procedure needs a database object as input, is to specify it in a way that regtype understands: foo is looked for in the search_path, foo.bar is interpreted as schema-qualified object, "foo.bar", including the quotes, is a name with a dot to be found in the search_path, "foo-bar"."qux.quux"... you get the idea. This is what postgres tools such as pgdump do to disambiguate input objects passed as parameters.

An implementation along this line can be found in psycopg 3: see the where t.oid = %(name)s::regtype.

So yes: I agree that this function should be fixed. However here we are in Hyrum realm: this function has been broken for 12 years and I'm sure (by the fallout of changing the way copy takes tables names see - #1294) that someone is relying on its current behaviour just as it is. So I would:

  • look for the type using the current algorithm
  • if not found, look for the type using the regtype algorithm

Does it sound ok?

pking-moj referenced this issue in ministryofjustice/laa-crime-application-store Jan 15, 2024
Bumps [psycopg2-binary](https://github.com/psycopg/psycopg2) from 2.9.7
to 2.9.9.
<details>
<summary>Changelog</summary>
<p><em>Sourced from <a
href="https://github.com/psycopg/psycopg2/blob/master/NEWS">psycopg2-binary's
changelog</a>.</em></p>
<blockquote>
<h2>Current release</h2>
<p>What's new in psycopg 2.9.9
^^^^^^^^^^^^^^^^^^^^^^^^^^^</p>
<ul>
<li>Add support for Python 3.12.</li>
<li>Drop support for Python 3.6.</li>
</ul>
<p>What's new in psycopg 2.9.8
^^^^^^^^^^^^^^^^^^^^^^^^^^^</p>
<ul>
<li>Wheel package bundled with PostgreSQL 16 libpq in order to add
support for
recent features, such as <code>sslcertmode</code>.</li>
</ul>
<p>What's new in psycopg 2.9.7
^^^^^^^^^^^^^^^^^^^^^^^^^^^</p>
<ul>
<li>Fix propagation of exceptions raised during module initialization

(:ticket:<code>[#1598](https://github.com/psycopg/psycopg2/issues/1598)</code>).</li>
<li>Fix building when pg_config returns an empty string
(:ticket:<code>[#1599](https://github.com/psycopg/psycopg2/issues/1599)</code>).</li>
<li>Wheel package bundled with OpenSSL 1.1.1v.</li>
</ul>
<p>What's new in psycopg 2.9.6
^^^^^^^^^^^^^^^^^^^^^^^^^^^</p>
<ul>
<li>Package manylinux 2014 for aarch64 and ppc64le platforms, in order
to
include libpq 15 in the binary package
(:ticket:<code>[#1396](https://github.com/psycopg/psycopg2/issues/1396)</code>).</li>
<li>Wheel package bundled with OpenSSL 1.1.1t.</li>
</ul>
<p>What's new in psycopg 2.9.5
^^^^^^^^^^^^^^^^^^^^^^^^^^^</p>
<ul>
<li>Add support for Python 3.11.</li>
<li>Add support for rowcount in MERGE statements in binary packages

(:ticket:<code>[#1497](https://github.com/psycopg/psycopg2/issues/1497)</code>).</li>
<li>Wheel package bundled with OpenSSL 1.1.1r and PostgreSQL 15
libpq.</li>
</ul>
<p>What's new in psycopg 2.9.4
^^^^^^^^^^^^^^^^^^^^^^^^^^^</p>
<ul>
<li>Fix <code>~psycopg2.extras.register_composite()</code>,
<code>~psycopg2.extras.register_range()</code> with customized
:sql:<code>search_path</code>

(:ticket:<code>[#1487](https://github.com/psycopg/psycopg2/issues/1487)</code>).</li>
<li>Handle correctly composite types with names or in schemas requiring
escape.</li>
</ul>
<!-- raw HTML omitted -->
</blockquote>
<p>... (truncated)</p>
</details>
<details>
<summary>Commits</summary>
<ul>
<li><a
href="https://github.com/psycopg/psycopg2/commit/ad5bee7054519d87f25bc5828c502b2ebe197049"><code>ad5bee7</code></a>
chore: bump version number to 2.9.9</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/37d1de1c8f446478666258f4370315f554efa16f"><code>37d1de1</code></a>
chore: add support for Python 3.12</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/abf2723c0a9d89740b433bf49b82ad765e87d5d4"><code>abf2723</code></a>
chore: drop support for Python 3.6</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/2da65a715c71fa5a242fce016835ba500376bf28"><code>2da65a7</code></a>
chore: drop leftover Python 2.7 import aliases from setup.py</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/3fa60fd26839a7e8b083ed3eb3dc592a0f2604f6"><code>3fa60fd</code></a>
chore: bump doc requirement complained by dependabot</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/1c1484e43bdd4927ac5725d4f250b198fbe7600f"><code>1c1484e</code></a>
ci: better interaction with scaleway build server</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/c81cec604f467dfb4fc87c9cbaaf4b758b4ca740"><code>c81cec6</code></a>
chore: bump to next dev release</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/7fe8cb77ca68a06c96d4551c6c9f8cab33904d2a"><code>7fe8cb7</code></a>
chore: bump docs requirements dependabot complains about</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/b39d5d64923a3e321672847bd57c002145f57430"><code>b39d5d6</code></a>
chore: bundle libpq 16</li>
<li><a
href="https://github.com/psycopg/psycopg2/commit/921510d5be7bc2f2dbf7ff3ce613ad0623de79f2"><code>921510d</code></a>
docs: replace &quot;compiled against&quot; with &quot;bundled with&quot;
in news file</li>
<li>Additional commits viewable in <a
href="https://github.com/psycopg/psycopg2/compare/2.9.7...2.9.9">compare
view</a></li>
</ul>
</details>
<br />


[![Dependabot compatibility
score](https://dependabot-badges.githubapp.com/badges/compatibility_score?dependency-name=psycopg2-binary&package-manager=pip&previous-version=2.9.7&new-version=2.9.9)](https://docs.github.com/en/github/managing-security-vulnerabilities/about-dependabot-security-updates#about-compatibility-scores)

Dependabot will resolve any conflicts with this PR as long as you don't
alter it yourself. You can also trigger a rebase manually by commenting
`@dependabot rebase`.

[//]: # (dependabot-automerge-start)
[//]: # (dependabot-automerge-end)

---

<details>
<summary>Dependabot commands and options</summary>
<br />

You can trigger Dependabot actions by commenting on this PR:
- `@dependabot rebase` will rebase this PR
- `@dependabot recreate` will recreate this PR, overwriting any edits
that have been made to it
- `@dependabot merge` will merge this PR after your CI passes on it
- `@dependabot squash and merge` will squash and merge this PR after
your CI passes on it
- `@dependabot cancel merge` will cancel a previously requested merge
and block automerging
- `@dependabot reopen` will reopen this PR if it is closed
- `@dependabot close` will close this PR and stop Dependabot recreating
it. You can achieve the same result by closing it manually
- `@dependabot show <dependency name> ignore conditions` will show all
of the ignore conditions of the specified dependency
- `@dependabot ignore this major version` will close this PR and stop
Dependabot creating any more for this major version (unless you reopen
the PR or upgrade to it yourself)
- `@dependabot ignore this minor version` will close this PR and stop
Dependabot creating any more for this minor version (unless you reopen
the PR or upgrade to it yourself)
- `@dependabot ignore this dependency` will close this PR and stop
Dependabot creating any more for this dependency (unless you reopen the
PR or upgrade to it yourself)


</details>

Signed-off-by: dependabot[bot] <support@github.com>
Co-authored-by: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants